from sqlalchemy.orm import relationship
from .db import Base
from sqlalchemy import Column, Integer, String, DateTime, ForeignKey, Boolean, Index
from datetime import datetime, timedelta, timezone

# 东八区时区对象（中国标准时间）
CST = timezone(timedelta(hours=8))


def now_cst():
    """返回当前北京时间（带时区信息）"""
    return datetime.now(CST)


class User(Base):
    __tablename__ = "users"

    # 优化点：添加组合索引提升查询性能
    __table_args__ = (
        # 组合索引：用于注册时的唯一性检查（一次查询检查username, phone, email）
        Index('idx_user_unique_check', 'username', 'phone', 'email'),
        # 单独的role索引：用于按角色筛选用户（如查询所有医生）
        Index('idx_user_role', 'role'),
        # 医生ID索引：用于查询某个医生的所有患者
        Index('idx_user_doctor_id', 'doctor_id'),
        # MySQL字符集设置
        {'mysql_charset': 'utf8mb4', 'mysql_collate': 'utf8mb4_unicode_ci'}
    )

    id = Column(Integer, primary_key=True, index=True)
    username = Column(String(50), unique=True, index=True, nullable=False)
    password = Column(String(255), nullable=False)
    email = Column(String(100), unique=True, index=True, nullable=True)
    phone = Column(String(20), unique=True, index=True, nullable=True)
    role = Column(String(20), default="user", nullable=False)  # 现在有专门索引
    created_at = Column(DateTime, default=now_cst())
    doctor_id = Column(Integer, ForeignKey("users.id", ondelete="SET NULL", onupdate="CASCADE"), nullable=True)

    doctor = relationship("User", remote_side=[id], backref="patients")

    def __repr__(self):
        return f"<User(username={self.username}, role={self.role})>"


class Alert(Base):
    __tablename__ = "alerts"

    # 优化点：为alerts表添加常用查询索引
    __table_args__ = (
        # 组合索引：按设备查询未解决的报警
        Index('idx_alert_device_resolved', 'device_id', 'resolved'),
        # 组合索引：按用户查询未解决的报警
        Index('idx_alert_owner_resolved', 'owner', 'resolved'),
        # 时间戳索引：按时间排序查询
        Index('idx_alert_timestamp', 'timestamp'),
        # MySQL字符集设置
        {'mysql_charset': 'utf8mb4', 'mysql_collate': 'utf8mb4_unicode_ci'}
    )

    id = Column(Integer, primary_key=True, autoincrement=True)
    device_id = Column(String(64), ForeignKey("devices.device_id", ondelete="CASCADE", onupdate="CASCADE"))
    owner = Column(String(50), ForeignKey("users.username", ondelete="CASCADE", onupdate="CASCADE"))
    message = Column(String(255))
    timestamp = Column(DateTime, default=datetime.utcnow)
    resolved = Column(Boolean, default=False)
    ecg_record_id = Column(Integer, ForeignKey("ecg_records.id", ondelete="CASCADE", onupdate="CASCADE"))